package com.office.automate.dataaccess.impl.user;

import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.office.automate.dataaccess.def.user.EmployeeDao;
import com.office.automate.dataaccess.framework.DataAccessSessionFactory;
import com.office.automate.exception.employee.EmployeeExceptionKeys;
import com.office.automate.exception.framework.FrontierVisibleException;
import com.office.automate.frontier.utility.DataConversionUtility;
import com.office.automate.model.employee.Employee;
import com.office.automate.model.employee.EmployeeAttandanceCommit;
import com.office.automate.model.employee.EmployeeAttandanceQueryObject;
import com.office.automate.model.employee.EmployeeOverTime;
import com.office.automate.model.employee.EmployeeQualification;
import com.office.automate.model.employee.EmployeeSearch;
import com.office.automate.model.leave.LieuLeaveRequest;
import com.office.automate.statics.ApplicationStatics;

public class EmployeeDaoImplementer implements EmployeeDao {

	private SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
	private static SessionFactory sessionFactory = null;
	private Session session = null;

	static {
		sessionFactory = DataAccessSessionFactory.getSessionFactory();
	}

	public EmployeeDaoImplementer() {
		session = sessionFactory.openSession();		
	}

	public void persistEmployeeDetails(Employee anEmployee) throws FrontierVisibleException {
		Transaction transaction = session.beginTransaction();
		session.save(anEmployee);
		transaction.commit();
	}

	public synchronized void getNextEmployeeNumber(Employee anEmployee) throws FrontierVisibleException {

		String SQL_QUERY = "select max(employee.employeeId) from Employee employee";
		Query query = session.createQuery(SQL_QUERY);
		Long nextEmployeeId = (Long) query.list().get(0);
		anEmployee.setEmployeeId(nextEmployeeId + 1);
	}

	public boolean isEmployeeExist(long anEmployeeNumber) throws FrontierVisibleException {
		
		String SQL_QUERY = "select count(employee.employeeId) from Employee employee where employeeId = :empId";
		Query query = session.createQuery(SQL_QUERY);
		query.setParameter("empId", anEmployeeNumber);
		return (Long)query.list().get(0) > 0;
	}

	public String getEmployeeName(long anEmployeeNumber) throws FrontierVisibleException {
		
		try {
			String SQL_QUERY = "select employee.firstName || ' ' || employee.lastName as FullName from Employee employee where employeeId = :empId";
			Query query = session.createQuery(SQL_QUERY);
			query.setParameter("empId", anEmployeeNumber);
			return (String) query.list().get(0);
		} catch (Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_EMPLOYEE_DETAILS, e);
		}
	}

	public void persistEmployeeQualificationDetails(EmployeeQualification anEmployeeQualification) throws FrontierVisibleException {
		Transaction transaction = session.beginTransaction();
		session.save(anEmployeeQualification);
		transaction.commit();
	}

	public void deleteQualification(EmployeeQualification anEmployeeQualification) throws FrontierVisibleException {
		try {
			session.beginTransaction();   
			session.delete(anEmployeeQualification);  
			session.getTransaction().commit();  
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_DELETION_QUALIFICATION, e);
		} finally {
			session.flush();
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public List<EmployeeQualification> getEmployeeQualificationPage(int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<EmployeeQualification> anEmployeeQualificationList = null;
		try {
			 String SQL_QUERY ="from EmployeeQualification employeeQualification order by employeeQualification.employeeId asc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeQualificationList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_QUALIFICATION, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeQualificationList;
	}

	public int getAvailableEmployeeQualificationDetailRecordCount() throws FrontierVisibleException {
		
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from EmployeeQualification employeeQualification";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_QUALIFICATION, e);
		}
		return recordCount;
	}

	@SuppressWarnings("unchecked")
	public List<Employee> getSearchResultPage(EmployeeSearch anEmployeeSearch, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<Employee> employeesList = null;
		StringBuffer buf = new StringBuffer("from Employee employee");
		try {
			if (anEmployeeSearch.getEmployeeId() > 0) {
				buf.append(" employee.employeeId=" + anEmployeeSearch.getEmployeeId());
			}
			if (anEmployeeSearch.getFirstName() != null && !"null".equals(anEmployeeSearch.getFirstName().toString()) && !anEmployeeSearch.getFirstName().isEmpty()) {
				buf.append(" and upper(employee.firstName) like upper('%" + anEmployeeSearch.getFirstName() + "%')");
			}
			if (anEmployeeSearch.getMiddleName() != null && !"null".equals(anEmployeeSearch.getMiddleName().toString()) && !anEmployeeSearch.getMiddleName().isEmpty()) {
				buf.append(" and upper(employee.middleName) like upper('%" + anEmployeeSearch.getMiddleName() + "%')");
			}
			if (anEmployeeSearch.getLastName() != null && !"null".equals(anEmployeeSearch.getLastName().toString()) && !anEmployeeSearch.getLastName().isEmpty()) {
				buf.append(" and upper(employee.lastName) like upper('%" + anEmployeeSearch.getLastName() + "%')");
			}
			if (anEmployeeSearch.getNic() != null && !"null".equals(anEmployeeSearch.getNic().toString()) && !anEmployeeSearch.getNic().isEmpty()) {
				buf.append(" and upper(employee.nic) like upper('%" + anEmployeeSearch.getNic() + "%')");
			}
			if (anEmployeeSearch.getEmail() != null && !"null".equals(anEmployeeSearch.getEmail().toString()) && !anEmployeeSearch.getEmail().isEmpty()) {
				buf.append(" and upper(employee.email) like upper('%" + anEmployeeSearch.getEmail() + "%')");
			}
			buf.append(" order by employee.employeeId asc");
			String SQL_QUERY  = buf.toString().indexOf("order")>27? new DataConversionUtility().replaceWord(buf.toString(), "Employee employee", "Employee employee where"): buf.toString();
			SQL_QUERY = new DataConversionUtility().replaceWord(SQL_QUERY, "where and", "where");
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 employeesList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_EMPLOYEE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return employeesList;
	}

	public int getAvailableEmployeeDetailRecordCount(EmployeeSearch aSearchCriteria) throws FrontierVisibleException {

		int recordCount = 0;
		try {
			StringBuffer buf = new StringBuffer("select count(*) from Employee employee");
			if (aSearchCriteria.getEmployeeId() > 0) {
				buf.append(" employee.employeeId=" + aSearchCriteria.getEmployeeId());
			}
			if (aSearchCriteria.getFirstName() != null && !"null".equals(aSearchCriteria.getFirstName().toString()) && !aSearchCriteria.getFirstName().isEmpty()) {
				buf.append(" and upper(employee.firstName) like upper('%" + aSearchCriteria.getFirstName() + "%')");
			}
			if (aSearchCriteria.getMiddleName() != null && !"null".equals(aSearchCriteria.getMiddleName().toString()) && !aSearchCriteria.getMiddleName().isEmpty()) {
				buf.append(" and upper(employee.middleName) like upper('%" + aSearchCriteria.getMiddleName() + "%')");
			}
			if (aSearchCriteria.getLastName() != null && !"null".equals(aSearchCriteria.getLastName().toString()) && !aSearchCriteria.getLastName().isEmpty()) {
				buf.append(" and upper(employee.lastName) like upper('%" + aSearchCriteria.getLastName() + "%')");
			}
			if (aSearchCriteria.getNic() != null && !"null".equals(aSearchCriteria.getNic().toString()) && !aSearchCriteria.getNic().isEmpty()) {
				buf.append(" and upper(employee.nic) like upper('%" + aSearchCriteria.getNic() + "%')");
			}
			if (aSearchCriteria.getEmail() != null && !"null".equals(aSearchCriteria.getEmail().toString()) && !aSearchCriteria.getEmail().isEmpty()) {
				buf.append(" and upper(employee.email) like upper('%" + aSearchCriteria.getEmail() + "%')");
			}
			buf.append(" order by employee.employeeId asc");
			String SQL_QUERY  = buf.toString().indexOf("order")>42? new DataConversionUtility().replaceWord(buf.toString(), "Employee employee", "Employee employee where"): buf.toString();
			SQL_QUERY = new DataConversionUtility().replaceWord(SQL_QUERY, "where and", "where");
			 Query queryResult = session.createQuery(SQL_QUERY);
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_EMPLOYEE_DETAILS, e);
		}
		return recordCount;
	}

	public void deleteEmployee(Employee anEmployee) throws FrontierVisibleException {
		try {
			session.beginTransaction();   
			session.delete(anEmployee);
			session.getTransaction().commit();  
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_DELETION_QUALIFICATION, e);
		} finally {
			session.flush();
			session.close();
		}
	}

	public Employee getEmployeeDetails(long anEmployeeId) throws FrontierVisibleException {
		Employee employee = null;
		try {
			 String SQL_QUERY ="from Employee employee where employee.employeeId= :employeeId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", anEmployeeId);
			 employee = (Employee) queryResult.uniqueResult();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_QUALIFICATION, e);
		} finally {
			session.flush();
			session.close();
		}
		return employee;
	}

	public void updateEmployee(Employee anUpdateEmployee) throws FrontierVisibleException {
		try {
			Employee employee = new EmployeeDaoImplementer().getEmployeeDetails(anUpdateEmployee.getEmployeeId());
			anUpdateEmployee.setUsername(employee.getUsername());
			anUpdateEmployee.setPassword(employee.getPassword());
			anUpdateEmployee.setImage(employee.getImage());
			session.beginTransaction();   
			session.update(anUpdateEmployee);
			session.getTransaction().commit();  
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_EMPLOYEE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	public List<EmployeeQualification> getEmployeeQualificationDetails(long anEmployeeId) throws FrontierVisibleException {
		List<EmployeeQualification> anEmployeeQualificationList = null;
		try {
			 String SQL_QUERY ="from EmployeeQualification employeeQualification where employeeQualification.employeeId= :empId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("empId", anEmployeeId);
			 anEmployeeQualificationList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_QUALIFICATION, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeQualificationList;
	}

	public void uploadProfileImage(Employee updateEmployee) throws FrontierVisibleException {
		try {
			Employee employee = new EmployeeDaoImplementer().getEmployeeDetails(updateEmployee.getEmployeeId());
			employee.setImage(updateEmployee.getImage());
			session.beginTransaction();   
			session.update(employee);
			session.getTransaction().commit();  
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_EMPLOYEE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
	}

	@Override
	public Map<String, Object> addEmployeeOTRequest(EmployeeOverTime employeeOverTime) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			Transaction transaction = session.beginTransaction();
			session.save(employeeOverTime);
			transaction.commit();
			emailRequiredData.put("OTRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getEmployeeId()));
			emailRequiredData.put("OTApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getApproverEmployeeId()));
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_ADDING_OT_REQUEST, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public Map<String, Object> approveOTRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			EmployeeOverTime employeeOverTime = new EmployeeDaoImplementer().getOTRequestDetails(aRequestId);
			employeeOverTime.setStatus(ApplicationStatics.APPROVED);
			session.beginTransaction();   
			session.update(employeeOverTime);
			session.getTransaction().commit();

			emailRequiredData.put("OTRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getEmployeeId()));
			emailRequiredData.put("OTApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getApproverEmployeeId()));
			emailRequiredData.put("EmployeeOverTime", employeeOverTime);

		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public Map<String, Object> rejectOTRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			EmployeeOverTime employeeOverTime = new EmployeeDaoImplementer().getOTRequestDetails(aRequestId);
			employeeOverTime.setStatus(ApplicationStatics.REJECTED);
			session.beginTransaction();   
			session.update(employeeOverTime);
			session.getTransaction().commit();
			emailRequiredData.put("OTRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getEmployeeId()));
			emailRequiredData.put("OTApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getApproverEmployeeId()));
			emailRequiredData.put("EmployeeOverTime", employeeOverTime);
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public Map<String, Object> cancelOTRequest(long aOTRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			EmployeeOverTime employeeOverTime = new EmployeeDaoImplementer().getOTRequestDetails(aOTRequestId);
			employeeOverTime.setStatus(ApplicationStatics.CANCEL);
			session.beginTransaction();   
			session.update(employeeOverTime);
			session.getTransaction().commit();
			emailRequiredData.put("OTRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getEmployeeId()));
			emailRequiredData.put("OTApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(employeeOverTime.getApproverEmployeeId()));
			emailRequiredData.put("EmployeeOverTime", employeeOverTime);
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public EmployeeOverTime getOTRequestDetails(long aOTRequestId) throws FrontierVisibleException {
		EmployeeOverTime employeeOverTime = null;
		try {
			 String SQL_QUERY ="from EmployeeOverTime employeeOverTime where employeeOverTime.requestId= :requestId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("requestId", aOTRequestId);
			 employeeOverTime = (EmployeeOverTime) queryResult.uniqueResult();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return employeeOverTime;
	}

	@Override
	public int getAvailableEmployeeOverTimeRequestsRecordCount(Map<String, Object> aQueryCriterias) throws FrontierVisibleException {
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from EmployeeOverTime employeeOverTime where employeeOverTime.employeeId= :employeeId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aQueryCriterias.get("EmployeeId"));
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		}
		return recordCount;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<EmployeeOverTime> getEmployeeOverTimeRequestsList(Map<String, Object> aQueryCriterias, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<EmployeeOverTime> anEmployeeOverTimeRequestsList = null;
		try {
			 String SQL_QUERY ="from EmployeeOverTime employeeOverTime where employeeOverTime.employeeId= :employeeId order by employeeOverTime.requestId desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aQueryCriterias.get("EmployeeId"));
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeOverTimeRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeOverTimeRequestsList;
	}

	@Override
	public int getAvailableEmployeeOverTimeApprovalRequestRecordCount(long aCurrentLoginUserId) throws FrontierVisibleException {
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from EmployeeOverTime employeeOverTime where employeeOverTime.approverEmployeeId= :employeeId and employeeOverTime.status= :Status";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aCurrentLoginUserId);
			 queryResult.setParameter("Status", ApplicationStatics.PENDING_APPROVAL);
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		}
		return recordCount;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<EmployeeOverTime> getEmployeeOverTimeApprovalRequestsList(long aCurrentLoginUserId, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<EmployeeOverTime> anEmployeeOverTimeRequestsList = null;
		try {
			 String SQL_QUERY ="from EmployeeOverTime employeeOverTime where employeeOverTime.approverEmployeeId= :employeeId and employeeOverTime.status= :Status order by employeeOverTime.requestId desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aCurrentLoginUserId);
			 queryResult.setParameter("Status", ApplicationStatics.PENDING_APPROVAL);
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeOverTimeRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeOverTimeRequestsList;
	}

	@Override
	public void commitAttandanceDetails(EmployeeAttandanceCommit employeeAttandanceCommit) throws FrontierVisibleException {
		try {
			Transaction transaction = session.beginTransaction();
			session.save(employeeAttandanceCommit);
			transaction.commit();			
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_ADDING_ATTANDANCE_REQUEST, e);
		} finally {
			session.flush();
			session.close();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<EmployeeAttandanceCommit> getAttandanceDetails(EmployeeAttandanceQueryObject attandanceQuaryObject) throws FrontierVisibleException {
		List<EmployeeAttandanceCommit> anEmployeeAttandanceCommitListForWeek = null;
		try {
			 String SQL_QUERY ="from EmployeeAttandanceCommit employeeAttandanceCommit where employeeAttandanceCommit.employeeId= :employeeId and employeeAttandanceCommit.attandanceDate between to_date(:StartDate,'YYYY-MM-DD') and to_date(:EndDate,'YYYY-MM-DD')";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", attandanceQuaryObject.getEmployeeId());
			 queryResult.setParameter("StartDate", attandanceQuaryObject.getStartDateOfWeek());
			 queryResult.setParameter("EndDate", attandanceQuaryObject.getEndDateOfWeek());
			 anEmployeeAttandanceCommitListForWeek = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeAttandanceCommitListForWeek;

	}

	@Override
	public EmployeeAttandanceCommit getAttandanceDetails(LieuLeaveRequest aLieuLeaveRequest) throws FrontierVisibleException {
		EmployeeAttandanceCommit anEmployeeAttandanceCommit = null;
		try {
			 String SQL_QUERY ="from EmployeeAttandanceCommit employeeAttandanceCommit where employeeAttandanceCommit.employeeId= :employeeId and employeeAttandanceCommit.attandanceDate= to_date(:StartDate,'YYYY-MM-DD') and employeeAttandanceCommit.swappingLevel like :swappingLevel";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aLieuLeaveRequest.getEmployeeId());
			 queryResult.setParameter("StartDate", formatter.format(aLieuLeaveRequest.getLieuLeaveDate()));
			 queryResult.setParameter("swappingLevel", "%In");
			 anEmployeeAttandanceCommit = (EmployeeAttandanceCommit) queryResult.uniqueResult();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_OT_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeAttandanceCommit;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Employee> getAllEmployeeDetail() throws FrontierVisibleException {
		List<Employee> anEmployeeList = null;
		try {
			 String SQL_QUERY ="from Employee employee where employee.username <> 'administrator' order by employee.employeeId asc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 anEmployeeList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_QUALIFICATION, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeList;
	}
}